#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.ServiceProcess;
using Microsoft.Win32;

#endregion

//============================================================================
//  File:    SQLServiceList.cs
//
//  Summary: Class to hold the instance and service data
//
//----------------------------------------------------------------------------
//
//  Copyright (C) sqldbatips.com.  All rights reserved.
//
//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
//  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.
//============================================================================


namespace SQL2005_Service_Manager
{
	//used to hold the instances and services related to SQL Server on the local machine
	public class SQLServiceList
	{

		#region Private members

		private DataTable m_dt;		//holds the instance and service data
		private String servername = System.Environment.MachineName;	//local servername

        private const String instancesbasekey = @"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL";
        private const String instancebasekey = @"SOFTWARE\Microsoft\Microsoft SQL Server\";
        private const String EXPRESS = "Express";

		#endregion

		#region Public members

		public const String MSDTC = "MSDTC";
		public const String SQL_SERVER = "SQL Server";
		public const String SQL_AGENT = "SQL Agent";
		public const String FTSEARCH = "Full Text Search";
		public const String REPORT = "Reporting Services";
		public const String ANALYSIS = "Analysis Services";
        public const String SSIS = "SSIS(2005) Service";
        public const String SSIS2008 = "SSIS(2008) Service";
        public const String BROWSER = "SQL Browser";

		#endregion

		#region Constructor

		public SQLServiceList()
		{
			//populate the instance and service data 
			BuildAndLoadTable();
		}

		#endregion

		#region Public Methods

        //return instances as list
        public string InstanceList()
        {
            StringBuilder sb = new StringBuilder();
            DataView dv = new DataView(SelectDistinct("InstanceList", m_dt, "InstanceName"));
            dv.Sort = "InstanceName";

            foreach (DataRowView drv in dv)
            {
                sb.Append(drv[0].ToString() + ",");
            }
            return sb.ToString().TrimEnd(",".ToCharArray());
        }

		//return instances
		public DataView Instances()
		{
			DataView dv = new DataView(SelectDistinct("InstanceList",m_dt,"InstanceName"));
			dv.Sort = "InstanceName";
			return dv;
		}

		//return services for a specific instance
		public DataView Services(String instancename)
		{
			DataView dv = new DataView(m_dt);
			dv.RowFilter = "InstanceName = '" + instancename + "'";
			dv.Sort = "DisplayName ASC";
			return dv;
		}

		// translate service name to friendly name
        public String ServiceToDisplay(String servicename)
		{
            String svc = servicename.ToUpper();

			if (svc == "MSDTC")
				return MSDTC;
			if (svc == "MSSQLSERVER" || svc.StartsWith("MSSQL$"))
				return SQL_SERVER;
            if (svc == "MSFTESQL" || svc.StartsWith("MSFTESQL$") || svc == "MSSEARCH" || svc == "MSSQLFDLAUNCHER" || svc.StartsWith("MSSQLFDLAUNCHER$"))
				return FTSEARCH;
			if (svc == "SQLSERVERAGENT" || svc.StartsWith("SQLAGENT$"))
				return SQL_AGENT;
			if (svc == "REPORTSERVER" || svc.StartsWith("REPORTSERVER$"))
				return REPORT;
			if (svc == "MSSQLSERVEROLAPSERVICE" || svc.StartsWith("MSOLAP$"))
				return ANALYSIS;
            if (svc.StartsWith("MSDTSSERVER"))
            {
                switch (svc)
                {
                    case "MSDTSSERVER100":
                        return SSIS2008;
                    default:
                        return SSIS;
                }
            }                
            if (svc == "SQLBROWSER")
                return BROWSER;
			// default
			return SQL_SERVER;
		}

		#endregion

		#region Private Methods

		//determines if a service is related to SQL Server i.e. one we want to manage
		private bool IsRelaventService(String servicename)
		{
			String svc = servicename.ToUpper();

            if (svc == "MSSQLSERVER" || svc.StartsWith("MSSQL$"))
			{
				return true;
			}
			else if (svc == "SQLSERVERAGENT" || svc.StartsWith("SQLAGENT$"))
			{
				return true;
			}
			else if (svc == "MSDTC")
			{
				return true;
			}
            else if (svc.StartsWith("MSDTSSERVER"))
            {
                return true;
            }
            else if (svc.StartsWith("MSFTESQL") || svc == "MSSEARCH")
			{
				return true;
			}
			else if (svc.StartsWith("REPORTSERVER"))
			{
				return true;
			}
            else if (svc == "SQLBROWSER")
            {
                return true;
            }
            else if (svc.StartsWith("MSOLAP") || svc == "MSSQLSERVEROLAPSERVICE")
			{
				return true;
			}
            else if (svc.StartsWith("MSSQLFDLAUNCHER") || svc == "MSSQLFDLAUNCHER")
            {
                return true;
            }
			else
			{
				return false;
			}
		}

		//enumerates all services on local server
		private void GetServices()
		{
			ServiceController[] services;

			try
			{
				services = ServiceController.GetServices();

				for (int i = 0; i < services.Length; i++)
				{
					AddServiceToList(services[i].ServiceName);
				}
			}
			catch
			{

			}
			finally
			{
				services = null;
			}
		}

		//if we're interested in the service add it to our datatable
		private void AddServiceToList(string servicename)
		{
			if (IsRelaventService(servicename) == true)
			{
				m_dt.Rows.Add(new Object[] { servicename, ServiceToInstance(servicename), ServiceToDisplay(servicename) });
			}
		}

		//creates the datatable schema and loads the data
		private void BuildAndLoadTable()
		{
			m_dt = new DataTable("ServiceList");
			DataColumn dcServiceName = new DataColumn("ServiceName", Type.GetType("System.String"));
			DataColumn dcInstanceName = new DataColumn("InstanceName", Type.GetType("System.String"));
			DataColumn dcDisplayName = new DataColumn("DisplayName", Type.GetType("System.String"));
			m_dt.Columns.Add(dcServiceName);
			m_dt.Columns.Add(dcInstanceName);
			m_dt.Columns.Add(dcDisplayName);

			DataColumn[] key = new DataColumn[1];
			key[0] = dcServiceName;
			m_dt.PrimaryKey = key;

			GetServices();
            Filter2008ExpressAgent();
		}

        //enumerate SQL instances to find SQL 2008 Express Edition instances
        private void Filter2008ExpressAgent()
        {
            String edition;
            String version;

            try
            {
                RegistryKey rk = Registry.LocalMachine.OpenSubKey(instancesbasekey);
                String[] instances = rk.GetValueNames();

                foreach (String ins in instances)
                {
                    RegistryKey rki = Registry.LocalMachine.OpenSubKey(instancebasekey + rk.GetValue(ins).ToString() + @"\Setup");
                    edition = rki.GetValue("Edition").ToString();
                    version = rki.GetValue("Version").ToString();
                    rki.Close();

                    if (!String.IsNullOrEmpty(edition) && !String.IsNullOrEmpty(version))
                    {
                        if (version.StartsWith("10.") && edition.StartsWith(EXPRESS, StringComparison.InvariantCultureIgnoreCase))
                            RemoveSQLAgent(ins);
                    }
                }
                rk.Close();
            }
            catch {}
        }

        //remove SQLAgent service for SQL 2008 Express instances as it can't be managed
        private void RemoveSQLAgent(String instance)
        {
            String service = "SQLAgent$" + instance;

            DataRow row = m_dt.Rows.Find(service);

            if (null != row)
            {
                m_dt.Rows.Remove(row);
            }
        }

		//convert servicename into a SQL type instance name ($ is the SQL service instance separator)
		private String ServiceToInstance(String svc)
		{
			if (svc.Contains("$") == true)
			{
				return servername + "\\" + svc.Split(new Char[] { '$' })[1];
			}
			else
			{
				return servername;
			}
		}

		// Compares two values to see if they are equal. Also compares DBNULL.Value.
		private bool ColumnEqual(object A, object B)
		{
			if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
				return true;
			if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
				return false;
			return (A.Equals(B));  // value type standard comparison
		}

		//helper method to emulate SQL select distinct - used to get distinct instance names
		private DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
		{
			DataTable dt = new DataTable(TableName);
			dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

			object LastValue = null;
			foreach (DataRow dr in SourceTable.Select("", FieldName))
			{
				if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
				{
					LastValue = dr[FieldName];
					dt.Rows.Add(new object[] { LastValue });
				}
			}
			return dt;
		}

		#endregion

	}
}
